
# 下面显示饼图时，时间区间可以修改，标题相应也要手动修改

import matplotlib as mpl
mpl.rcParams['font.sans-serif'] = ['SimHei']
mpl.rcParams['axes.unicode_minus'] = False
import pymysql
import matplotlib.pyplot as plt
import numpy as np
from prettytable import PrettyTable
import pandas as pd

def generate_pie_chart():
    conn = pymysql.connect(
        host='10.114.183.55',
        user='labuser',
        password='123456',
        db='exceltodb',
        port=3306,
        charset='utf8'
    )
    cursor = conn.cursor()
    
    # 查询所有产品的分类工时数据
    query = """
    SELECT 
        test_category, 
        SUM(Test_hour * Coefficient) as category_hours
    FROM assignment
    WHERE 
        Date BETWEEN '2025-04-01' AND '2025-06-30' 
        AND product != '' 
        AND test_category != 'Operation' 
        AND special != 's'
    GROUP BY test_category
    """
    cursor.execute(query)
    results = cursor.fetchall()
    conn.close()
    
    # 定义类别规则
    category_order_base = ['Full ALT', 'ALT Eval', 'DVT', 'DVT Eval', 'CALT', 'CDVT', 'Carriers', 'FCNT', 'Others']
    carriers_subcategories = ['TMO', 'ATT', 'Softbank', 'Docomo']
    
    # 处理数据（合并所有产品）
    category_data = {cat: 0 for cat in category_order_base}
    
    for category, hours in results:
        hours = int(round(hours, 0))  # 保留整数
        if category in carriers_subcategories:
            category_data['Carriers'] += hours
        elif category in category_order_base:
            category_data[category] += hours
        else:
            category_data['Others'] += hours
    
    # 过滤0工时类别
    filtered_data = {k: v for k, v in category_data.items() if v > 0}
    
    # 分离Others并按工时降序排列其他类别
    if 'Others' in filtered_data:
        others_value = filtered_data.pop('Others')
        sorted_items = sorted(filtered_data.items(), key=lambda x: x[1], reverse=True)
        sorted_items.append(('Others', others_value))
        sorted_data = dict(sorted_items)
    else:
        sorted_data = dict(sorted(filtered_data.items(), key=lambda x: x[1], reverse=True))
    
    # 计算总工时和占比
    total_hours = sum(sorted_data.values())
    percentage_data = {k: (v/total_hours)*100 for k, v in sorted_data.items()}
    
    # 生成核对表格
    table_columns = ['类别', '工时', '占比']
    check_table = PrettyTable(table_columns)
    check_table.align['类别'] = 'l'
    check_table.align['工时'] = 'r'
    check_table.align['占比'] = 'r'
    
    for cat in sorted_data:
        check_table.add_row([cat, sorted_data[cat], f'{percentage_data[cat]:.1f}%'])
    check_table.add_row(['总计', total_hours, '100%'])
    
    print("\n===== 类别工时汇总表 =====")
    print(check_table)
    
    # 导出Excel
    try:
        excel_data = [{'类别': cat, '工时': sorted_data[cat], '占比(%)': percentage_data[cat]} for cat in sorted_data]
        df = pd.DataFrame(excel_data)
        df.to_excel('类别工时汇总数据.xlsx', index=False)
        print(f"\n详细数据已导出至：类别工时汇总数据.xlsx")
    except Exception as e:
        print(f"\n导出Excel失败：{str(e)}")
    
    # 创建实心饼图
    fig, ax = plt.subplots(figsize=(10, 7))
    
    # 颜色方案
    colors = [
        '#3498db',  # 蓝色
        '#2ecc71',  # 绿色
        '#f39c12',  # 橙色
        '#e74c3c',  # 红色
        '#9b59b6',  # 紫色
        '#1abc9c',  # 青绿色
        '#34495e',  # 深蓝色
        '#f1c40f',  # 黄色
        '#95a5a6'   # 灰色 (Others专用)
    ]
    
    # 确保Others使用灰色
    color_list = []
    for cat in sorted_data:
        if cat == 'Others':
            color_list.append(colors[-1])  # 最后一个颜色是灰色
        else:
            idx = list(sorted_data.keys()).index(cat)
            color_list.append(colors[idx % (len(colors)-1)])  # 前8个颜色循环使用
    
    # 绘制饼图
    wedges, texts, autotexts = ax.pie(
        sorted_data.values(),
        labels=sorted_data.keys(),
        colors=color_list,
        autopct='',
        startangle=90,
        counterclock=False,
        wedgeprops=dict(edgecolor='white', linewidth=1)
    )
    
    # 自定义标签：同时显示工时和百分比
    for i, (wedge, text, autotext) in enumerate(zip(wedges, texts, autotexts)):
        cat = list(sorted_data.keys())[i]
        hours = sorted_data[cat]
        pct = percentage_data[cat]
        autotext.set_text(f'{hours}\n({pct:.1f}%)')
        autotext.set_fontsize(9)
        autotext.set_color('white' if pct > 5 else 'black')
        autotext.set_weight('bold')
    
    # 美化标签
    plt.setp(texts, size=10)
    
    # 标题
    ax.set_title(f'2025年二季度各类别工时分布（总工时：{total_hours}）', pad=20)
    
    # 确保饼图为正圆形
    ax.axis('equal')
    
    # 图例放入图中右上角
    ax.legend(
        sorted_data.keys(),
        title='测试类别',
        loc='upper right',
        bbox_to_anchor=(0.95, 0.95),
        fontsize=9,
        frameon=True,
        framealpha=0.9,
        facecolor='white'
    )
    
    plt.tight_layout()
    plt.show()

generate_pie_chart()